import json
import pymysql
import requests

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3'
}

connection = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='cityData',
                             charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)


def grossDomesticProduct():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0201%22%7D%5D&k1=1711711799849&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(亿元)
    code_mapping = {
        'A020101': "地区生产总值",
        'A020102': "第一产业增加值",
        'A020103': "第二产业增加值",
        'A020104': "第三产业增加值",
        'A020105': "农林牧渔业增加值",
        'A020106': "工业增加值",
        'A020107': "建筑业增加值",
        'A020108': "批发和零售业增加值",
        'A02010A': "交通运输、仓储和邮政业增加值",
        'A02010C': "住宿和餐饮业增加值",
        'A02010D': "金融业增加值",
        'A02010E': "房地产业增加值",
        'A02010F': "其他行业增加值",
        'A02010G': "人均地区生产总值",
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `economic` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '亿元'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def employmentSituation():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0401%22%7D%5D&k1=1711712437077&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(万人)
    code_mapping = {
        'A040101': "城镇单位就业人员",
        'A040102': "农林牧渔业城镇单位就业人员",
        'A040103': "采矿业城镇单位就业人员",
        'A040104': "制造业城镇单位就业人员",
        'A040105': "电力、热力、燃气及水生产和供应业城镇单位就业人员",
        'A040106': "建筑业城镇单位就业人员",
        'A040107': "交通运输、仓储和邮政业城镇单位就业人员",
        'A040108': "信息传输、软件和信息技术服务业城镇单位就业人员",
        'A040109': "批发和零售业城镇单位就业人员",
        'A04010A': "住宿和餐饮业城镇单位就业人员",
        'A04010B': "金融业城镇单位就业人员",
        'A04010C': "房地产业城镇单位就业人员",
        'A04010D': "租赁和商务服务业城镇单位就业人员",
        'A04010E': "科学研究和技术服务业城镇单位就业人员",
        'A04010F': "水利、环境和公共设施管理业城镇单位就业人员",
        'A04010G': "居民服务、修理和其他服务业城镇单位就业人员",
        'A04010H': "教育业城镇单位就业人员",
        'A04010I': "卫生和社会工作城镇单位就业人员",
        'A04010J': "文化、体育和娱乐业城镇单位就业人员",
        'A04010K': "公共管理、社会保障和社会组织城镇单位就业人员",
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 4)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `population` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '万人'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()

    print(data)


def averageSalary():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A040A%22%7D%5D&k1=1711712871069&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(元)
    code_mapping = {
        'A040A01': "城镇单位就业人员平均工资",
        'A040A02': "农林牧渔业城镇单位就业人员平均工资",
        'A040A03': "采矿业城镇单位就业人员平均工资",
        'A040A04': "制造业城镇单位就业人员平均工资",
        'A040A05': "电力、热力、燃气及水生产和供应业城镇单位就业人员平均工资",
        'A040A06': "建筑业城镇单位就业人员平均工资",
        'A040A07': "交通运输、仓储和邮政业城镇单位就业人员平均工资",
        'A040A08': "信息传输、软件和信息技术服务业城镇单位就业人员平均工资",
        'A040A09': "批发和零售业城镇单位就业人员平均工资",
        'A040A0A': "住宿和餐饮业城镇单位就业人员平均工资",
        'A040A0B': "金融业城镇单位就业人员平均工资",
        'A040A0C': "房地产业城镇单位就业人员平均工资",
        'A040A0D': "租赁和商务服务业城镇单位就业人员平均工资",
        'A040A0E': "科学研究和技术服务业城镇单位就业人员平均工资",
        'A040A0F': "水利、环境和公共设施管理业城镇单位就业人员平均工资",
        'A040A0G': "居民服务、修理和其他服务业城镇单位就业人员平均工资",
        'A040A0H': "教育业城镇单位就业人员平均工资",
        'A040A0I': "卫生和社会工作城镇单位就业人员平均工资",
        'A040A0J': "文化、体育和娱乐业城镇单位就业人员平均工资",
        'A040A0K': "公共管理、社会保障和社会组织城镇单位就业人员平均工资",
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': i['data']['data']}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `economic` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '元'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


def consumptionLevel():
    url = 'https://data.stats.gov.cn/easyquery.htm?m=QueryData&dbcode=fsnd&rowcode=zb&colcode=sj&wds=%5B%7B%22wdcode%22%3A%22reg%22%2C%22valuecode%22%3A%22440000%22%7D%5D&dfwds=%5B%7B%22wdcode%22%3A%22zb%22%2C%22valuecode%22%3A%22A0A02%22%7D%5D&k1=1711713655957&h=1'

    res = requests.get(url, headers=headers, verify=False, timeout=30)
    datas = list(json.loads(res.text)['returndata']['datanodes'])

    # 映射代码和对应的人口数据类型(元)
    code_mapping = {
        'A0A0201': "全体居民人均消费支出",
        'A0A0202': "城镇居民人均消费支出",
        'A0A0203': "农村居民人均消费支出"
    }
    cursor = connection.cursor()
    data = {category: [] for category in code_mapping.values()}
    for i in datas[1:]:
        if i['data']['data'] == 0 or i['wds'][0]['valuecode'] not in code_mapping.keys():
            continue
        d = {'year': i['wds'][2]['valuecode'], 'value': round(i['data']['data'], 2)}
        data[code_mapping[i['wds'][0]['valuecode']]].append(d)
        sql = ("INSERT INTO `economic` VALUES ('{}','{}','{}','{}','{}')"
               .format(i['wds'][2]['valuecode'], i['data']['data'], code_mapping[i['wds'][0]['valuecode']], '广东省',
                       '元'))
        try:
            cursor.execute(sql)
        except:
            pass
    connection.commit()
    cursor.close()
    print(data)


if __name__ == "__main__":
    employmentSituation()
    averageSalary()
    consumptionLevel()
